package src.com.service;

import src.com.data.ClickCoordinate;
import src.com.data.User;
import src.com.util.DBOmanagerTBRAI;
import src.com.util.DBOmanagerTCRAI;
import src.com.util.DBOmanagerUsers;
import src.com.util.FtpAllFile;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class MyService {
    int a = 1;
    DBOmanagerUsers dbOmanager = new DBOmanagerUsers();
    //DBOmanagerData dbOmanagerData = new DBOmanagerData();
    DBOmanagerTCRAI dbOmanagerTCRAI = new DBOmanagerTCRAI();
//    用户登录_旧
    public int login(User user){
        dbOmanager.initDB();
        dbOmanager.getConn();
        ResultSet rs = null;
        int result = 0;
        String sql = "select *from users where name = ? and password = ?";
        PreparedStatement ps = dbOmanager.createPrepareStatement(sql);
        try {
            ps.setString(1,user.getName());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
            if(rs.next()){
                result = 1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanager.closeDB();
        }
        return result;
    }

    //用户注册_旧
    public int  register(User user){
        dbOmanager.initDB();
        dbOmanager.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        if((user.getName()==null)||user.getPassword()==null){
            return 0;
        }
        String initSql = "select *from users where name = ?";
        ps = dbOmanager.createPrepareStatement(initSql);
        try {
            ps.setString(1,user.getName());
            rs = ps.executeQuery();
            if(!rs.next()){
                String sql = "insert into users (name,password)values(?,?)";
                ps = dbOmanager.createPrepareStatement(sql);
                try {
                    ps.setString(1,user.getName());
                    ps.setString(2,user.getPassword());
                    result = ps.executeUpdate();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    dbOmanager.closeDB();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanager.closeDB();
        }
        return result;
    }

    //获取单词的坐标
    public List getCoordinate(int pageNum){                     //单词
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String[]> arrays =new ArrayList();
        String sql = "select coord from note_words where page_num = ?";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setString(1, String.valueOf(pageNum));
            rs = ps.executeQuery();
            while(rs.next()){  //要用while，不能用if
               // System.out.println(rs.getString("coord"));
                String []temp = new String[10];
                String index = rs.getString("coord");
                temp = index.split(";");
                for(int i=0;i<temp.length;i++){
                    //System.out.println(temp[i]);
                    String[] temp1 = temp[i].split("[(,)]");
                    //System.out.println(temp1.length);
                    temp1[0] = temp1[1];
                    temp1[1] = temp1[2];
                    temp1[2] = temp1[9];
                    temp1[3] = temp1[10];
                    arrays.add(temp1);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return arrays;
    }
//获取单词坐标2
    public List getWordCoordinate(String text) {
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String[]> arrays = new ArrayList<>();
        int count = 0;
        String sql = "select coord from note_words where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if (rs != null) {
                System.out.println("单词坐标请求成功");
                while (rs.next()) {
                    String [] temp=new String[10];
                    String index = rs.getString("coord");
                    temp=index.split(";");
                    for(int i=0;i<temp.length;i++){
                        String[] temp1 = temp[i].split("[(,)]");
                        //System.out.println(temp1.length);
                        temp1[0] = temp1[1];
                        temp1[1] = temp1[2];
                        temp1[2] = temp1[9];
                        temp1[3] = temp1[10];
                        arrays.add(temp1);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbOmanagerTCRAI.closeDB();
        }
        return arrays;
    }

    public int getPageNum(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        int num = 0;
        String sql=  "select max(page_num) from all_pages";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            while (rs.next()){
                num = rs.getInt("max(page_num)");
                System.out.println("长度："+num);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return num;
    }

    public List getWordsEnglish(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_words";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    public List getWordsChinese(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        String sql = "select trans from note_words";
        List<String> lists = new ArrayList<>();
        int count = 0;
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("请求成功");
                while(rs.next()){
//                    System.out.println(rs.getString("trans"));
                    lists.add(rs.getString("trans"));
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    public List getEnglishByfre(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_words order by freq desc";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    public List getChineseByfre(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select trans from note_words order by freq desc";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("请求成功");
                while(rs.next()){
                    String index = rs.getString("trans");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lists;
    }

    public boolean getPhoto(){
        FtpAllFile ftpAllFile = new FtpAllFile("192.168.1.113",21,"wit","0000");
        ftpAllFile.ftpLogin();
        int page_num;
        String path = null;
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        PreparedStatement ps = null;
        ResultSet  rs = null;
        String sql = "select *from all_pages";
        ps =dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            while(rs.next()){
                //if(rs.getInt("is_noted")==1){
                    page_num = rs.getInt("page_num");
                    path = "/home/wit/share/save1save"+page_num+".png";
                    System.out.println(path);
                    ftpAllFile.downLoadDirectory(page_num,"D:/Postgraduate/Project/BRAI/image", "/home/wit/share/save1save");
               // }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }


        ftpAllFile.ftpLogOut();
        return true;
    }

    public String getClickWord(ClickCoordinate coordinate){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String[]> arrays =new ArrayList();
        int count = 0;
        float coordinateX = coordinate.getX();              //坐标X点
        float coordinateY = coordinate.getY();              //坐标Y点
        int  page_num = coordinate.getPage_num(); //页码
        System.out.println(coordinateX+","+coordinateY+","+page_num);
        int coordinateRow = 0;                              //所传坐标的行号
        String resultString = null;
        int coordinatePara = 0;

        String sqlPara = "select *from all_paras where page_num = ?";
        ps = dbOmanagerTCRAI.createPrepareStatement(sqlPara);
        try {
            ps.setInt(1,page_num);
            rs = ps.executeQuery();
            while(rs.next()){
                String index = rs.getString("coord");
                String[] temp1 = index.split("[(,)]");
//                System.out.println(temp1.length);
//                for(String coor:temp1){
//                    System.out.println(coor);
//                }
               //System.out.println(temp1[2]+","+temp1[6]);
                if((coordinateY>=Float.parseFloat(temp1[6])&&coordinateY<=Float.parseFloat(temp1[2]))){
                    coordinatePara = rs.getInt("para_num");
                   // System.out.println("para_num"+","+coordinatePara);
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        String sql = "select * from all_rows where page_num = ? and para_num = ?";             //通过传递的页码判断要取的数据
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setInt(1,page_num);
            ps.setInt(2,coordinatePara);
            rs = ps.executeQuery();
                while(rs.next()){                                       //要用while，不能用if
                    // System.out.println(rs.getString("coord"));
                    String []temp = new String[10];
                    String index = rs.getString("coord");
                        String[] temp1 = index.split("[(,)]");
//                    System.out.println(temp1[14]+","+temp1[6]+"=======");
                        if((coordinateY>Float.parseFloat(temp1[6]))&&(coordinateY<Float.parseFloat(temp1[14]))){  //判断点击坐标在第几行
                            coordinateRow = rs.getInt("row_num");          //行
                            System.out.println("行："+coordinateRow);
                        }
                 //   }
                }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        sql = "select * from all_words where row_num = ? and page_num = ? and para_num = ?" ;
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setInt(1,coordinateRow);
            ps.setInt(2,page_num);
            ps.setInt(3,coordinatePara);
            rs = ps.executeQuery();
            while (rs.next()){
                // System.out.println(rs.getString("coord"));
                String []temp = new String[10];
                String index = rs.getString("coord");
                temp = index.split(";");
               // for(int i=0;i<temp.length;i++){
                    //System.out.println(temp[i]);
                    String[] temp1 = index.split("[(,)]");
                    //System.out.println(temp1.length);
                    if((coordinateX>Float.parseFloat(temp1[1]))&&(coordinateX<Float.parseFloat(temp1[9]))){  //判断点击坐标在第几行
                        resultString = rs.getString("content");
                        System.out.println("结果："+resultString);
                    }
               // }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return resultString;
    }

    //获取段落点的坐标
    public List getParagraphCoordinate(int pageNum){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String[]> arrays =new ArrayList();
        String sql = "select * from note_paragraph where page_num = ?";
        ArrayList<Integer> paraNums = new ArrayList<>();
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setString(1, String.valueOf(pageNum));
            rs = ps.executeQuery();
            while(rs.next()){                                       //要用while，不能用if

                int para_num = rs.getInt("para_num");
                paraNums.add(para_num);
                System.out.println("段落："+para_num);
            }

            for(int i=0;i<paraNums.size();i++){
                sql = "select *from all_rows where page_num = ? and para_num = ?";
                ps = dbOmanagerTCRAI.createPrepareStatement(sql);
                ps.setInt(1,pageNum);
                ps.setInt(2, paraNums.get(i));
                rs = ps.executeQuery();
                while(rs.next()){
                    System.out.println(rs.getString("coord"));
                    String []temp = new String[10];
                    String index = rs.getString("coord");
                    String[] temp1 = index.split("[(,)]");
                    temp1[0] = temp1[1];
                    temp1[1] = temp1[2];
                    temp1[2] = temp1[5];
                    temp1[3] = temp1[6];
                    arrays.add(temp1);
                    //arrays.add(temp1);
                }
            }

//            for(int i=0;i<arrays.size();i++){
//                for(int j=0;j<arrays.get(i).length;j++){
//                    System.out.println(arrays.get(i)[j]);
//                }
//            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return arrays;
    }

    public List getParagraph(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_paragraph";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("段落请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //sql = "select"
        return lists;
    }

    public List getSentence(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_sentences";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("句子请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //sql = "select"
        return lists;
    }

    //获取sentence点的坐标
    public List getSentenceCoordinate(int pageNum){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String[]> arrays =new ArrayList();
        String sql = "select * from note_sentences where page_num = ?";
        ArrayList<Integer> paraNums = new ArrayList<>();
        ArrayList<Integer> senNums = new ArrayList<>();
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setString(1, String.valueOf(pageNum));
            rs = ps.executeQuery();
            while(rs.next()){                                       //要用while，不能用if

                int para_num = rs.getInt("para_num");
                paraNums.add(para_num);
                System.out.println("段落："+para_num);
                int sen_num = rs.getInt("sen_num");
                senNums.add(sen_num);
                System.out.println("句子："+sen_num);
            }

            for(int i=0;i<paraNums.size();i++){
                for(int j=0;j<senNums.size();j++) {
                    sql = "select *from note_sentences where page_num = ? and para_num = ? and sen_num = ?";
                    ps = dbOmanagerTCRAI.createPrepareStatement(sql);
                    ps.setInt(1, pageNum);
                    ps.setInt(2, paraNums.get(i));
                    ps.setInt(3, senNums.get(j));
                    rs = ps.executeQuery();
                    while (rs.next()) {
                        System.out.println(rs.getString("coord"));
                        String[] temp = new String[10];
                        String index = rs.getString("coord");
                        String[] temp1 = index.split("[(,)]");
                        temp1[0] = temp1[1];
                        temp1[1] = temp1[2];
                        temp1[2] = temp1[5];
                        temp1[3] = temp1[6];
                        arrays.add(temp1);
                        //arrays.add(temp1);
                    }
                }
            }

//            for(int i=0;i<arrays.size();i++){
//                for(int j=0;j<arrays.get(i).length;j++){
//                    System.out.println(arrays.get(i)[j]);
//                }
//            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return arrays;
    }

    public List getParagraphByFre(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_paragraph order by freq desc";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("频率段落请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    public List getSentenceImageNumber(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;

        String sql = "select page_num from note_sentences where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("句子图片号请求成功");
                while(rs.next()){
                    String index = rs.getString("page_num");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }
    public List getWordDate(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select date from note_words where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("单词日期请求成功");
                while(rs.next()){
                    String index = rs.getString("date");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;


    }

    public List getWordFreq(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select freq from note_words where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("单词频率请求成功");
                while(rs.next()){
                    String index = rs.getString("freq");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }


    public List getWordTrans(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select trans from note_words where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("单词意思请求成功");
                while(rs.next()){
                    String index = rs.getString("trans");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }
    public List getWordImageNumber(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;

        String sql = "select page_num from note_words where content= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("单词图片号请求成功");
                while(rs.next()){
                    String index = rs.getString("page_num");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }
    public List getSentenceByFre(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_sentences order by freq desc";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("频率句子请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }



    //补充页面翻转后的段的坐标
    public List getLastParagraphCoordinate(int pageNum){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> arrays =new ArrayList();
        String sql = "select para_num from note_paragraph where page_num = ?";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            ps.setString(1, String.valueOf(pageNum));
            rs = ps.executeQuery();
            while(rs.next()){                                       //要用while，不能用if
                arrays.add("存在段落");
            }
//            for(int i=0;i<arrays.size();i++){
//                for(int j=0;j<arrays.get(i).length;j++){
//                    System.out.println(arrays.get(i)[j]);
//                }
//            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return arrays;
    }


    // 通过user_id获得句子
    public List getSentence_User(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_sentences where user_id= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("用户id句子请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }
    // 通过user_id获得单词列表
    public List getWord_User(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_words where user_id= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("用户id单词请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    // 通过user_id获得单词释义列表
    public List getWord_Chinese_User(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select trans from note_words where user_id= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("用户id单词释义列表请求成功");
                while(rs.next()){
                    String index = rs.getString("trans");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    // 通过user_id获得段落列表
    public List getParagraph_User(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from note_paragraph where user_id= '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("用户id段落列表请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }
    public List getComments_User(String text){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "SELECT content FROM comments WHERE user_id <>  '"+text+"'";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("通过id评论获取成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            dbOmanagerTCRAI.closeDB();
        }
        return lists;
    }

    public List getComments(){
        dbOmanagerTCRAI.initDB();
        dbOmanagerTCRAI.getConn();
        int result = 0;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<String> lists = new ArrayList<>();
        int count = 0;
        String sql = "select content from comments";
        ps = dbOmanagerTCRAI.createPrepareStatement(sql);
        try {
            rs = ps.executeQuery();
            if(rs!=null){
                System.out.println("评论请求成功");
                while(rs.next()){
                    String index = rs.getString("content");
                    lists.add(index);
                    count++;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //sql = "select"
        return lists;
    }

    public int getUserId(User user){
        dbOmanager.initDB();
        dbOmanager.getConn();
        ResultSet rs = null;
        int result = 0;
        String sql = "SELECT id FROM users WHERE name = ? AND password = ?";
        PreparedStatement ps = dbOmanager.createPrepareStatement(sql);
        try {
            ps.setString(1,user.getName());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
            if(rs.next()){
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanager.closeDB();
        }
        return result;
    }

    // 用户登陆
    public int sign_in(User user){
        dbOmanager.initDB();
        dbOmanager.getConn();
        ResultSet rs = null;
        int result = 0;
        String sql = "SELECT id FROM users WHERE name = ? AND password = ?";
        PreparedStatement ps = dbOmanager.createPrepareStatement(sql);
        try {
            ps.setString(1,user.getName());
            ps.setString(2,user.getPassword());
            rs = ps.executeQuery();
            if(rs.next()){
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            dbOmanager.closeDB();
        }
        return result;
    }

    // 用户注册
    public boolean sign_up(User user) {
        dbOmanager.initDB();
        dbOmanager.getConn();
        int rowsAffected = 0;
        boolean result = false;
        String sql = "INSERT INTO users (name, password) VALUES (?, ?)";
        PreparedStatement ps = dbOmanager.createPrepareStatement(sql);

        try {
            ps.setString(1, user.getName());
            ps.setString(2, user.getPassword());
            rowsAffected = ps.executeUpdate(); // 使用 executeUpdate() 执行 INSERT 操作
            if (rowsAffected > 0) {
                result = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbOmanager.closeDB();
        }
        return result;
    }

}
